import pymysql
from time import sleep
import pandas as pd
import os
from pymysql.converters import escape_string
class toMysql:
    host='192.168.0.113'
    port=3306
    user='coname'
    passwd='xZS6p4LDcKCwn4Yb'
    charset='utf8'
    db='co'
    def __init__(self):#初始化读取excel
        self.wb=pd.read_excel(r'D:\wky\spider\humansource\humansource_detail\human2.xlsx',sheet_name="Sheet1")
        self.nums=self.wb.shape[0]+1

    def read(self):#获取excel的数据
        for i in range(1,self.nums,1):
            # keywordid=self.wb.loc[i,'keywordid']
            # fileurl=self.wb.loc[i,'fileurl']
            srname = self.wb.loc[i,'srname']
            content = self.wb.loc[i,'content']
            self.save_mysql(srname,content)

    def open_datas(self):#打开数据库
        try:
            self.conn=pymysql.Connection(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            self.cursor=self.conn.cursor()
            print("打开数据库成功!")
            return self.conn,self.cursor
        except Exception as e:
            print("打开数据库失败>>>",e)

            
    def close_datas(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            conn.close()
            print("关闭数据成功!")
        except Exception as e:
            print("关闭数据库失败>>>",e)

    def save_mysql(self,srname,content):#保存数据
        conn,cursor=self.open_datas()
        #插入数据
        # try:   
        #     if not cursor.execute('select srname from co_human_source where srname="{}"'.format(name)):
        #         cursor.execute('insert into co_human_source (keywordid,srname,fileurl) values("{}","{}","{}")'.format(id,name,url))
        #         conn.commit()
        #         print('录入到数据库成功!')
        #     else:
        #         pass
        # except Exception as e:
        #     print("录入到数据失败>>>",e)
        #更改数据
        try:
            cursor.execute('update co_human_source set content="{}" where srname="{}"'.format(escape_string(content),srname)) 
            conn.commit()
            print("更改数据成功!>>>"+srname)
        except Exception as e:
            print('更改数据失败>>>',e)

        self.close_datas(conn,cursor)

    def run(self):#运行
        self.read()

if __name__ == "__main__":
    toMysqler=toMysql()
    toMysqler.run()
    



        


